﻿# encoding:utf-8
# dict 转成数据库语句

import re, json
import pymysql, traceback

# 驼峰转下划线
# https://stackoverflow.com/questions/1175208/elegant-python-function-to-convert-camelcase-to-snake-case
def camelcase2Underscore(name):
    s1 = re.sub('(.)([A-Z][a-z]+)', r'\1_\2', name)
    return re.sub('([a-z0-9])([A-Z])', r'\1_\2', s1).lower()

# 数组转换,返回新数组
def camelcase2UnderscoreArray(array, keysExcept=None):
    newArray = []
    if keysExcept is None:
        for key in array:
            newArray.append(camelcase2Underscore(key))
    else:
        for key in array:
            if not key in keysExcept:
                newArray.append(camelcase2Underscore(key))

    return newArray

# dict 所有 key 驼峰转下划线,返回新 dict
def camelcase2UnderscoreDictKeys(dict, keysExcept=None):
    newDict = {}
    if keysExcept is None:
        for key in dict.keys():
            newDict[camelcase2Underscore(key)] = dict[key]
    else:
        for key in dict.keys():
            if not key in keysExcept:
                newDict[camelcase2Underscore(key)] = dict[key]

    return newDict

# 过滤后取出值数组
def valuesExcept(dict, keysExcept):
    newArray = []
    if keysExcept is None:
        for key in dict.keys():
            newArray.append(dict[key])
    else:
        for key in dict.keys():
            if not key in keysExcept:
                newArray.append(dict[key])

    return newArray

# 返回 dict 中 value 为 None 或 '' 的 key 组成的数据
# existArray: 可以传一个现成的数组
def getKeysWhileValueIsEmpty(dict, existArray=None):
    if existArray is None:
        existArray = []
    for key in dict.keys():
        if dict[key] is None or ''==dict[key]:
            existArray.append(key)

    return existArray

# 根据 python 变量类型返回对应在 mysql 的类型字符
def type2MysqlType(value):
    if type(value) is int:
        return 'INT(11) NOT NULL'
    if type(value) is float:
        return 'VARCHAR(32) NOT NULL'
    if type(value) is float:
        return 'VARCHAR(32) NOT NULL'

    return 'VARCHAR(256) NOT NULL'

# 根据 dict 生成创建数据库表语句
# keys dict.keys()
# tableName 数据库表名
def dict2SqlCreate(dict, tableName):
    sql = " CREATE TABLE `"+tableName+"` ("\
        + " `id` BIGINT(20) NOT NULL AUTO_INCREMENT,"\
        + "	`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,"\
        + " `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,"

    for key in dict.keys():
        sql = sql+" `"+camelcase2Underscore(key)+"` "+type2MysqlType(dict[key])+","

    sql += " PRIMARY KEY (`id`)"
    sql += " )"
    sql += " COLLATE=\'utf8mb4_unicode_ci\'"
    sql += " ENGINE=InnoDB"
    return sql
    
# 根据 dict 生成插入数据库表语句
# keys dict.keys()
# tableName 数据库表名
# keysExcept keys 中要排除的 key
def dict2SqlInsert(keys, tableName, keysExcept=None):
    tmpKeys = camelcase2UnderscoreArray(keys, keysExcept)
    placeholder = ", ".join(["%s"] * len(tmpKeys))
    sql = "insert into `{table}` ({columns}) values ({values});".format(table=tableName, columns=",".join(tmpKeys), values=placeholder)
    return sql

# 根据 dict 生成更新数据库表语句(不包含 where 语句)
# keys dict.keys()
# tableName 数据库表名
# keysExcept keys 中要排除的 key
def dict2SqlUpdate(keys, tableName, keysExcept=None):
    tmpKeys = camelcase2UnderscoreArray(keys, keysExcept)
    # 所有字段都被排除，不作任何更新
    if len(tmpKeys)>=len(keys):
        return "UPDATE `" + tableName + "` set puid=puid"
        
    sql = "UPDATE `" + tableName + "` set {}".format(', '.join('{}=%s'.format(k) for k in tmpKeys))
    return sql

# 将 keys 中 value 为 '' 的值去掉（返回新的，不改变旧的）
def exceptEmptyStringKeys(dict):
    tmpDict = {}
    for k in dict.keys():
        if ''!=dict[k]:
            tmpDict[k] = dict[k]

    return tmpDict

# 执行 sql ,返回是否成功
def testExeSql(sql):
    try:
        # 打开数据库连接
        db = pymysql.connect(
            host='localhost',
            port=3306,
            user='root',
            passwd='0026888',
            db='wxpy',
            charset='utf8'
        )
        cursor=db.cursor(cursor=pymysql.cursors.DictCursor)
        values = valuesExcept(dict, keysExcept)
        print(values)
        cursor.execute(sql,values)
        db.commit()
        cursor.close()
        db.close()
        return True
    except Exception as e:
        traceback.print_exc()
        print('------------------err: '+str(e))
        return False

if __name__ == "__main__":
    strJson = """
    {
        "Uin":0,
        "UserName":"@@8a7d7c22932070d4a698e274ae051467fb557f887a0e0d34fedddc062b13b089",
        "NickName":"金御苑物品闲置转让群 商家勿入",
        "AttrStatus":0,
        "PYInitial":"JYYWPXZZRQSJWR",
        "PYQuanPin":"jinyuyuanwupinxianzhizhuairangqunshangjiawuru",
        "RemarkPYInitial":"",
        "RemarkPYQuanPin":"",
        "DisplayName":"",
        "KeyWord":"",
        "HeadImgUrl":"/cgi-bin/mmwebwx-bin/webwxgetheadimg?seq=689577410&username=@@8a7d7c22932070d4a698e274ae051467fb557f887a0e0d34fedddc062b13b089&skey=",
        "ContactFlag":2,
        "MemberCount":475,
        "RemarkName":"",
        "HideInputBarFlag":0,
        "Sex":0,
        "Signature":"",
        "VerifyFlag":0,
        "OwnerUin":0,
        "StarFriend":0,
        "AppAccountFlag":0,
        "Statues":0,
        "Province":"",
        "City":"",
        "Alias":"",
        "SnsFlag":0,
        "UniFriend":0,
        "ChatRoomId":0,
        "EncryChatRoomId":"@732afa32c1558e0ef901518835a69a21",
        "IsOwner":0,
        "MemberList":[],
        "isAdmin":"None",
        "self":{},
        "HeadImgUpdateFlag":1,
        "ContactType":0,
        "ChatRoomOwner":"@b52b086b2afd2d80a4d1dd7d919adaf2e666ae6337f5d9ba57644898bbb0cdbc"
    }
    """

    dict = json.loads(strJson)
    # 自己增加的
    dict['is_group'] = 1
    dict['puid'] = 'test'
    # 非群用户才有的
    # sql = dict2SqlCreate(dict, "user_all")
    # print(sql)
    keysExcept = ['self','MemberList']
    sql = dict2SqlInsert(dict, "user_all", keysExcept)
    print(sql)
    testExeSql(sql)

    dict['avatar'] = 'www.testurl.jpg'
    dictUpdate = exceptEmptyStringKeys(dict)
    sql = dict2SqlUpdate(dictUpdate, "user_all", keysExcept)
    print(sql)
    testExeSql(sql)
    
    